/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package taskmanager2ndedition.server;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import taskmanager2ndedition.Task;
import taskmanager2ndedition.TaskInfo;
import taskmanager2ndedition.exceptions.ObjectNotFoundException;

/**
 *
 * @author Pavel
 */
public class MySQLTaskStorage implements Storage<Task> {

//    table Tasks - how it looks in MySQL database and numbers of columns
// 1 id: INTEGER;  = primary key
// 2 userId: INTEGER;
// 3 status: INTEGER;     ------------    HOW TO CONNECT WITH OUR ENUM?
// 4 description: VARCHAR;
// 5 expirationDate: TIMESTAMP;
// 6 name: VARCHAR;
// 7 contacts: VARCHAR;
// 8 version:  INTEGER;
// 9 modified: BIT;
// 10 state: INTEGER;
    private static java.sql.Connection conn;

    public MySQLTaskStorage() {
        try {
            String driver = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://localhost:3306/";
            String dbName = "TaskManagerDatabase";
            String userName = "root";
            String password = "";
            Class.forName(driver).newInstance();
            conn = DriverManager.getConnection(url + dbName, userName, password);
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public synchronized void remove(int id) throws ObjectNotFoundException {
        try {
            Statement stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM Tasks WHERE id = '" + id + "'");
        } catch (SQLException ex) {
            throw new ObjectNotFoundException();
        }
    }

    public synchronized Task get(int id) throws ObjectNotFoundException {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<Task> result = new ArrayList<Task>();
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM Tasks WHERE id = '" + id + "'");
            if (!rs.next()) 
                throw new ObjectNotFoundException();
            
            Task task = new Task();
            task.setId(id);
            task.setUserId( rs.getInt(2));
            task.setDescription( rs.getString(4));
            java.util.Date date = new java.util.Date(rs.getDate(5).getTime());
            task.setExpirationDate(date);
            task.setName( rs.getString(6));
            task.setContacts( rs.getString(7));
            task.setVersion( rs.getInt(8));
            task.setModified( rs.getBoolean(9));
            switch (rs.getInt(3)){
                case 1:
                    task.setStatus( TaskInfo.TaskStatus.InProgress);
                    break;
                case 2: 
                    task.setStatus(TaskInfo.TaskStatus.Closed);
                    break;
                case 0:
                default:
                    task.setStatus( TaskInfo.TaskStatus.Opened);
            }
            switch (rs.getInt(10)){
                case 1:
                    task.setState( TaskInfo.TaskState.Added);
                    break;
                case 2: 
                    task.setState(TaskInfo.TaskState.Removed);
                    break;
                case 0:
                default:
                    task.setState( TaskInfo.TaskState.Editted);
            }
            
            result.add(task);
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return result.get(0);
    }

    /**
     * Saves a new task into the database
     * 
     * @param obj    Task that should be saved
     *  
     */
    public synchronized void add(Task obj) {
        try {
            Statement stmt = conn.createStatement();
            int status = 0;
            int state = 0;
            if (obj.getStatus() == TaskInfo.TaskStatus.Opened) status = 0;
            if (obj.getStatus() == TaskInfo.TaskStatus.InProgress) status = 1; 
            if (obj.getStatus() == TaskInfo.TaskStatus.Closed) status = 2;
            if (obj.getState() == TaskInfo.TaskState.Editted) state = 0;
            if (obj.getState() == TaskInfo.TaskState.Added) state = 1; 
            if (obj.getState() == TaskInfo.TaskState.Removed) state = 2;
            java.sql.Date date = new java.sql.Date(obj.getExpirationDate().getTime());
            stmt.executeUpdate("INSERT INTO Tasks (id,userId,status,description,expirationDate,name,contacts,version,modified, state) VALUES ('" + obj.getId() + "','" + obj.getUserId()
                    + "','" + status + "','" + obj.getDescription() + "','" + date + "','" + obj.getName() + "','" + obj.getContacts() + "','" + obj.getVersion() + "'," + obj.isModified() + ",'" + state + "')");
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Updates the specified task from the database
     * 
     * @param obj    Task that should be updated
     *  
     */
    public synchronized void update(Task obj) {
        try {
            Statement stmt = conn.createStatement();
            int status = 0;
            int state = 0;
            if (obj.getStatus() == TaskInfo.TaskStatus.Opened) status = 0;
            if (obj.getStatus() == TaskInfo.TaskStatus.InProgress) status = 1; 
            if (obj.getStatus() == TaskInfo.TaskStatus.Closed) status = 2;
            if (obj.getState() == TaskInfo.TaskState.Editted) state = 0;
            if (obj.getState() == TaskInfo.TaskState.Added) state = 1; 
            if (obj.getState() == TaskInfo.TaskState.Removed) state = 2;
            java.sql.Date date = new java.sql.Date(obj.getExpirationDate().getTime());
            stmt.executeUpdate("UPDATE Tasks SET id = '" + obj.getId()
                    + "',userId = '" + obj.getUserId() + "',status = '" + status + "',description = '"
                    + obj.getDescription() + "',expirationDate = '" + date + "',name = '" + obj.getName() + "',contacts = '" + obj.getContacts() + "',version = '" + obj.getVersion() + "',modified = " + obj.isModified() + ",state = '" + state + "' WHERE id = '" + obj.getId()+ "'");
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Loads all tasks from the database
     * 
     * @return List of all the tasks in the storage     
     */
    public synchronized List<Task> getAll() {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<Task> result = new ArrayList<Task>();
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM Tasks");
            while (rs.next()) {
                Task task = new Task();
                task.setId(rs.getInt(1));
                task.setUserId( rs.getInt(2));
                task.setDescription( rs.getString(4));
                java.util.Date date = new java.util.Date(rs.getDate(5).getTime());
                task.setExpirationDate(date);
                task.setName( rs.getString(6));
                task.setContacts( rs.getString(7));
                task.setVersion( rs.getInt(8));
                task.setModified( rs.getBoolean(9));
                switch (rs.getInt(3)){
                    case 1:
                        task.setStatus( TaskInfo.TaskStatus.InProgress);
                        break;
                    case 2: 
                        task.setStatus(TaskInfo.TaskStatus.Closed);
                        break;
                    case 0:
                    default:
                        task.setStatus( TaskInfo.TaskStatus.Opened);
                }
                switch (rs.getInt(10)){
                    case 1:
                        task.setState( TaskInfo.TaskState.Added);
                        break;
                    case 2: 
                        task.setState(TaskInfo.TaskState.Removed);
                        break;
                    case 0:
                    default:
                        task.setState( TaskInfo.TaskState.Editted);
                }

                result.add(task);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return result;
    }

    /**
     * Saves all the tasks into the database
     * 
     * @param list    List of tasks that should be saved
     *  
     */
    public synchronized void saveAll(List<Task> list) {
        try {
            for (Task obj : list) {
                Statement stmt = conn.createStatement();
                int status = 0;
                int state = 0;
                if (obj.getStatus() == TaskInfo.TaskStatus.Opened) status = 0;
                if (obj.getStatus() == TaskInfo.TaskStatus.InProgress) status = 1; 
                if (obj.getStatus() == TaskInfo.TaskStatus.Closed) status = 2;
                if (obj.getState() == TaskInfo.TaskState.Editted) state = 0;
                if (obj.getState() == TaskInfo.TaskState.Added) state = 1; 
                if (obj.getState() == TaskInfo.TaskState.Removed) state = 2;
                java.sql.Date date = new java.sql.Date(obj.getExpirationDate().getTime());
                stmt.executeUpdate("INSERT INTO Tasks (id,userId,status,description,expirationDate,name,contacts,version,modified) VALUES ('" + obj.getId() + "','" + obj.getUserId()
                        + "','" + status + "','" + obj.getDescription() + "','" + date + "','" + obj.getName() + "','" + obj.getContacts() + "','" + obj.getVersion() + "'," + obj.isModified() + ",'" + state + "')");
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public synchronized List<Task> getGroup(int userId) {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<Task> result = new ArrayList<Task>();
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM Tasks WHERE userId = '" + userId + "'");
            while (rs.next()){            
                Task task = new Task();
                task.setId(rs.getInt(1));
                task.setUserId( rs.getInt(2));
                task.setDescription( rs.getString(4));
                java.util.Date date = new java.util.Date(rs.getDate(5).getTime());
                task.setExpirationDate(date);
                task.setName( rs.getString(6));
                task.setContacts( rs.getString(7));
                task.setVersion( rs.getInt(8));
                task.setModified( rs.getBoolean(9));
                switch (rs.getInt(3)){
                    case 1:
                        task.setStatus( TaskInfo.TaskStatus.InProgress);
                        break;
                    case 2: 
                        task.setStatus(TaskInfo.TaskStatus.Closed);
                        break;
                    case 0:
                    default:
                        task.setStatus( TaskInfo.TaskStatus.Opened);
                }
                switch (rs.getInt(10)){
                    case 1:
                        task.setState( TaskInfo.TaskState.Added);
                        break;
                    case 2: 
                        task.setState(TaskInfo.TaskState.Removed);
                        break;
                    case 0:
                    default:
                        task.setState( TaskInfo.TaskState.Editted);
                }

                result.add(task);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(MySQLUserStorage.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return result;
    }
}
